package sys_serv

import (
	"strconv"
	"strings"
	"ubt-aio-service/app/mod/sys_mod"
	"ubt-aio-service/app/pkg/db"
	"ubt-aio-service/app/utils"
)

type sysNote struct{}

var SysNoteServ = sysNote{}

// AddRel 批量新增消息与用户关联
func (s *sysNote) AddRel(req []*sys_mod.SysNoteUserReq) {
	sqlStr := "INSERT INTO sys_note_rel(note_id, user_id, states, add_time, upd_time) VALUES "
	var (
		param []any
		n     = 1
	)
	for _, rnu := range req {
		sqlStr += "($" + strconv.Itoa(n) + ", $" + strconv.Itoa(n+1) + ", 1, EXTRACT(epoch FROM NOW()), EXTRACT(epoch FROM NOW())),"
		param = append(param, rnu.NoteId)
		param = append(param, rnu.UserId)
		n += 2
	}
	sqlStr = sqlStr[:len(sqlStr)-1]
	db.CudData(sqlStr, param...)
}

// Process 处理消息
func (s *sysNote) Process(req *sys_mod.SysNoteProReq) *utils.ResJson {
	var (
		param  []any
		idList []string
		q      string
		n      = 1
	)
	param = append(param, req.States)
	idList = strings.Split(req.RelIds, ",")
	for _, curId := range idList {
		n++
		q += "$" + strconv.Itoa(n) + ","
		param = append(param, curId)
	}
	q = q[:len(q)-1]
	sqlStr := "UPDATE sys_note_rel SET states=$1, upd_time=EXTRACT(epoch FROM NOW()) WHERE rel_id IN (" + q + ")"
	return db.CudData(sqlStr, param...)
}

// One 获取消息详情
func (s *sysNote) One(noteId string) *utils.ResJson {
	sqlStr := "SELECT note_id, title, contents, add_time FROM sys_notice WHERE note_id=$1"
	return db.GetOne(sqlStr, noteId)
}

// Count 获取我的消息数量
func (s *sysNote) Count(req *sys_mod.SysNotePageReq) *utils.ResJson {
	sqlStr := "SELECT COUNT(1) AS nums FROM sys_note_rel a " +
		"LEFT JOIN sys_notice b ON a.note_id=b.note_id WHERE a.states IN (1, 3) AND a.user_id=$1"
	param := make([]any, 0)
	param = append(param, req.UserId)
	var n = 1
	if req.Keywords != "" {
		sqlStr += " AND ("
		nameArr := strings.Split(req.Keywords, " ")
		for _, name := range nameArr {
			n++
			sqlStr += "b.title LIKE '%'||$" + strconv.Itoa(n) + "||'%' OR "
			param = append(param, name)
		}
		sqlStr = sqlStr[:len(sqlStr)-4]
		sqlStr += ")"
	}
	if req.BeginTime > 0 {
		n++
		sqlStr += " AND a.add_time>=$" + strconv.Itoa(n)
		param = append(param, req.BeginTime)
	}
	if req.EndTime > 0 {
		n++
		sqlStr += " AND a.add_time<=$" + strconv.Itoa(n)
		param = append(param, req.EndTime)
	}
	if res := db.GetOne(sqlStr, param...); res.Code == utils.Succeed.Code && res.Data.(map[string]any)["nums"].(int64) > 0 {
		return res
	}
	return utils.NoData
}

// Page 获取消息分页列表
func (s *sysNote) Page(req *sys_mod.SysNotePageReq) *utils.ResJson {
	sqlStr := "SELECT a.rel_id, a.note_id, b.title, a.states, a.add_time FROM sys_note_rel a " +
		"LEFT JOIN sys_notice b ON a.note_id=b.note_id WHERE a.states IN (1, 3) AND a.user_id=$1"
	param := make([]any, 0)
	param = append(param, req.UserId)
	var n = 1
	if req.Keywords != "" {
		sqlStr += " AND ("
		nameArr := strings.Split(req.Keywords, " ")
		for _, name := range nameArr {
			n++
			sqlStr += "b.title LIKE '%'||$" + strconv.Itoa(n) + "||'%' OR "
			param = append(param, name)
		}
		sqlStr = sqlStr[:len(sqlStr)-4]
		sqlStr += ")"
	}
	if req.BeginTime > 0 {
		n++
		sqlStr += " AND a.add_time>=$" + strconv.Itoa(n)
		param = append(param, req.BeginTime)
	}
	if req.EndTime > 0 {
		n++
		sqlStr += " AND a.add_time<=$" + strconv.Itoa(n)
		param = append(param, req.EndTime)
	}
	sqlStr += " ORDER BY a.upd_time DESC LIMIT $" + strconv.Itoa(n+1) + " OFFSET $" + strconv.Itoa(n+2)
	param = append(param, req.Limit)
	param = append(param, req.Offset)
	return db.GetAll(sqlStr, param...)
}

// New 查询是否存在未读消息
func (s *sysNote) New(userId string) *utils.ResJson {
	sqlStr := "SELECT COUNT(1) AS nums FROM sys_note_rel WHERE states=1 AND user_id=$1"
	res := db.GetOne(sqlStr, userId)
	return &utils.ResJson{Code: utils.Succeed.Code, Msg: utils.Succeed.Msg, Data: res.Data.(map[string]any)["nums"].(int64)}
}

// Read 已读消息
func (s *sysNote) Read(snuId string) *utils.ResJson {
	sqlStr := "UPDATE sys_note_rel SET states=3, upd_time=EXTRACT(epoch FROM NOW()) WHERE rel_id=$1"
	return db.CudData(sqlStr, snuId)
}

// Add 新增消息
func (s *sysNote) Add(req *sys_mod.SysNoteSaveReq) {
	sqlStr := "INSERT INTO sys_notice(note_id, title, contents, user_ids, add_time) VALUES ($1, $2, $3, $4, EXTRACT(epoch FROM NOW()))"
	db.CudData(sqlStr, req.NoteId, req.Title, req.Contents, req.UserIds)
}

// RelSpecUser 关联指定用户
func (s *sysNote) RelSpecUser(ch chan int, noteId string) {
	sqlStr := "INSERT INTO sys_note_rel(note_id, user_id, states, add_time, upd_time) VALUES ($1, $2, 1, EXTRACT(epoch FROM NOW()), EXTRACT(epoch FROM NOW()))"
	db.CudData(sqlStr, noteId)
	<-ch
}

// RelRoleUser 关联指定角色用户
func (s *sysNote) RelRoleUser(ch chan int, noteId string) {
	sqlStr := "INSERT INTO sys_note_rel(note_id, user_id, states, add_time, upd_time) " +
		"SELECT $1 AS note_id, a.user_id, 1 AS states, EXTRACT(epoch FROM NOW()) AS add_time, EXTRACT(epoch FROM NOW()) AS upd_time " +
		"FROM sys_user a, sys_role b WHERE a.states=1 AND b.states=1 AND b.role_code='1002' AND a.role_id=b.role_id"
	db.CudData(sqlStr, noteId)
	<-ch
}
